create database 阶段二作业一;
go 
use 阶段二作业一;
go

--创建学生信息表
create table stuInfo(
stuID int identity primary key,
stuName nvarchar(10),
stuAge int,
stuSex tinyint,
time  datetime,
);

--创建课程信息表
create table courseInfo(
courseID int identity primary key,
courseName nvarchar(10),
courseMarks int );

--创建分数信息表
create table scoreInfo(
scoreID  int identity,
stuID int references stuInfo(stuID),
courseID int references courseInfo(courseID),
score int
);
insert into stuInfo(stuName ,stuAge ,stuSex ,time )
values('Tom',19,1,NULL),
      ('Jack',20,0,NULL),
      ('Rose',21,1,NULL),
      ('Lulu',19,1,NULL),
      ('Lili',21,0,NULL),
      ('abc',20,1,'2007-01-07 01:11:36.590');

insert into courseInfo(courseName,courseMarks)
values('JavaBase',4),
      ('HTML',2),
      ('JavaScript',2),
	  ('SqlBase',2);

insert into scoreInfo(stuID,courseID,score)
values(1,1,80),
      (1,2,85),
      (1,4,50),
      (2,1,75),
      (2,3,45),
      (2,4,75),
      (3,1,45),
      (4,1,95),
      (4,2,75),
	  (4,3,90),
	  (4,4,45);
select * from stuInfo
select * from scoreInfo
select * from courseInfo
--1.查询出每个学生所选修的课程的数量和所选修的课程的考试的平均分
select stuID,count(courseName)选修的课程的数量, avg(score)平均分 
from courseInfo inner join scoreInfo 
on courseInfo.courseID=scoreInfo.courseID 
group by stuID
--2.查询出每门课程的选修的学生的个数和学生考试的总分
select courseName,count(stuName)学生个数,sum(score) 考试总分
from stuInfo join scoreInfo 
on stuInfo.stuID=scoreInfo.stuID join courseInfo 
on courseInfo.courseID=scoreInfo.courseID 
group by courseName
--3.查询出性别一样并且年龄一样的学生的信息
select son.* from stuInfo son join stuInfo father 
on son.stuAge=father.stuAge and son.stuSex=father.stuSex
where son.stuID!=father.stuID ;
--4.查询出学分一样的课程信息
select distinct son.* from courseInfo son join courseInfo father 
on son.courseMarks=father.courseMarks 
where son.courseID!=father.courseID
--5.查询出参加了考试的学生的学号，姓名，课程号和分数
select scoreInfo.stuID,StuName,scoreInfo.courseID,score 
from stuInfo s join scoreInfo 
on s.stuID=scoreInfo.stuID join courseInfo 
on courseInfo.courseID=scoreInfo.courseID  
where exists(select 1 from scoreInfo r where r.stuID=s.stuID)
--6.查询出参加了考试的学生的学号，课程号，课程名，课程学分和分数
select scoreInfo.stuID,StuName,scoreInfo.courseID,courseName,courseMarks,score
from stuInfo s join scoreInfo 
on s.stuID=scoreInfo.stuID join courseInfo 
on courseInfo.courseID=scoreInfo.courseID  
where exists(select 1 from scoreInfo r where r.stuID=s.stuID)
--7.查询出没有参加考试的学生的学号和姓名
select stuID,StuName from stuInfo s where not exists(select 1 from scoreInfo r where r.stuID=s.stuID)
--8.查询出是周六周天来报到的学生

--9.查询出姓名中有字母a的学生的信息
select * from stuInfo where stuName like'%a%'
--10.查询出选修了2门课程以上的并且考试平均分在70以上的学生的学号和考试平均分以及选修课程的数量